6 Step 4 - Check Structures

7 Problem description

We must check if the camera trap Structure_id provided on the sheet Camera_trap is encompassed on the sheets that describe the structures (Underpasses and Overpasses). These sheets also possess the Structure_id field. This column is the link between Camera_trap and Underpasses / Overpasses.

All the listed Structure_id provided on the Camera_trap sheet must be present on either Underpasses or Overpasses. The opposite direction is also true, meaning that all the Structure_id provided either on Underpasses or Overpasses must be comprised on Camera_trap.

We also have to evaluate if the Structure_id present in the Fencing spreadsheet is included on the Underpasses spreadsheet. We are assuming that there are no fencing for Overpasses.

8 Problem solving

8.1 Common steps

To solve this issue, we follow some of the first basic steps from previous checks, as using our customized read_sheet function that provides the full paths of all .xlsx files available in order to read the species sheet from all files.

Code

source("R/FUNCTIONS.R")

Here we read the camera trap sheet from all files. We opted to bring the elements of the structures (field Structure_id) to upper case in order to avoid confusions. We also decided do keep only the column Structure_id by using the argument .keep.

Code

ct <- read_sheet(
  path = "Example",
  sheet = "Camera_trap",
  na = c("", "na", "NA")
) |>
  purrr::map(
    ~ .x |>
      dplyr::mutate(
        Structure_id = as.character(stringr::str_to_upper(Structure_id)),
        .keep = "none"
      )
  )

8.2 Specific steps

8.2.1 Check if there are blank rows on camera trap sheet

It is important to check if there are blank rows to avoid the system to find errors where there is no data.

Code

ct |>
  purrr::keep(~ nrow(.x) > 0) |>
  head(2)

$Example1
# A tibble: 435 × 1
   Structure_id 
   <chr>        
 1 BC1 (GALERIA)
 2 BC1 (GALERIA)
 3 BC1 (GALERIA)
 4 BC1 (GALERIA)
 5 BC1 (GALERIA)
 6 BC1 (GALERIA)
 7 BC1 (GALERIA)
 8 BC1 (GALERIA)
 9 BC1 (GALERIA)
10 BC1 (GALERIA)
# ℹ 425 more rows

$Example2
# A tibble: 7 × 1
  Structure_id
  <chr>       
1 CE2         
2 CE3         
3 CE4         
4 CE5         
5 CE6         
6 CE7         
7 CE9

8.2.2 Check duplicated names between underpasses and overpasses

We wouldn’t be able to perform a thorough validation if there are duplicated Structure_id between underpasses and overpasses. In that sense, we created two lists called under and over. We kept on the respective lists solely the datasets that comprise either of these types of structures.

Following this step we load all the underpasses spreadsheets from the files.

Code

under <- read_sheet(
  path = "Example",
  sheet = "Underpasses",
  na = c("", "na", "NA")
) |>
  purrr::map(
    ~ .x |>
      dplyr::mutate(
        Structure_id = as.character(stringr::str_to_upper(Structure_id)),
        position = "under",
        .keep = "none"
      )
  ) |>
  purrr::keep(~ all(nrow(.x) > 0))

over <- read_sheet(
  path = "Example",
  sheet = "Overpasses",
  na = c("", "na", "NA")
) |>
  purrr::map(
    ~ .x |>
      dplyr::mutate(
        Structure_id = as.character(stringr::str_to_upper(Structure_id)),
        position = "over",
        .keep = "none"
      )
  ) |>
  purrr::keep(~ nrow(.x) > 0)

head(under[1])

$Example1
# A tibble: 14 × 2
   Structure_id     position
   <chr>            <chr>   
 1 P1 (IGUAÇU)      under   
 2 BC1 (GALERIA)    under   
 3 P2 (MAURICIO)    under   
 4 BC2 (DRENAGEM)   under   
 5 BCS1             under   
 6 P3 (VARZEA)      under   
 7 P4 (FAZENDA)     under   
 8 P5 (SAPEZAL)     under   
 9 P6 (PASSA TRÊS)  under   
10 P7 (LOURENÇO)    under   
11 P8 (DAS PEDRAS)  under   
12 BCS2             under   
13 P9 (CACHORROS)   under   
14 P10 (AMOLA FACA) under

Code

head(over[1])

$Example1
# A tibble: 1 × 2
  Structure_id position
  <chr>        <chr>   
1 PAEREA       over

The next step is to merge underpasses and overpasses Structure_id from each dataset into a single dataframe.

Code

# Binding under and over in one only list
under_over <- list()

for (i in names(ct)) {
  exists_in_under <- i %in% names(under)
  exists_in_over <- i %in% names(over)

  if (exists_in_under & exists_in_over) {
    under_over[[i]] <- dplyr::bind_rows(under[[i]], over[[i]])
  } else if (exists_in_under & exists_in_over == FALSE) {
    under_over[[i]] <- under[[i]]
  } else {
    under_over[[i]] <- over[[i]]
  }
}

head(under_over[1])

$Example1
# A tibble: 15 × 2
   Structure_id     position
   <chr>            <chr>   
 1 P1 (IGUAÇU)      under   
 2 BC1 (GALERIA)    under   
 3 P2 (MAURICIO)    under   
 4 BC2 (DRENAGEM)   under   
 5 BCS1             under   
 6 P3 (VARZEA)      under   
 7 P4 (FAZENDA)     under   
 8 P5 (SAPEZAL)     under   
 9 P6 (PASSA TRÊS)  under   
10 P7 (LOURENÇO)    under   
11 P8 (DAS PEDRAS)  under   
12 BCS2             under   
13 P9 (CACHORROS)   under   
14 P10 (AMOLA FACA) under   
15 PAEREA           over

Now we can check if there are duplicated names for Structure_id. This checks if there are duplicates on overpasses, underpasses and the merge of underpasses and overpasses.

Code

under_over |>
  dplyr::bind_rows(.id = "Dataset") |>
  dplyr::count(Dataset, Structure_id, sort = TRUE) |>
  dplyr::filter(n > 1)

# A tibble: 2 × 3
  Dataset  Structure_id     n
  <chr>    <chr>        <int>
1 Example3 OAC24            2
2 Example3 OAC89            2

8.2.3 Check if the structures in Camera Trap sheet are in either Underpasses and Overpasses sheet and vice-versa

8.2.3.1 Camera trap x Crossing structures

Initially, we got from every dataset, the Structure_id from their camera trap spreadsheet and the same field from their underpasses and overpasses spreadsheet. We compared the strings from camera traps and underpasses and overpasses. We expect that for every string on Structure_id camera trap sheet to be included on either underpasses or overpasses sheet.

Code

ct_diff_under_over <- purrr::map(names(ct), function(x) {
  a <- ct[[x]]
  uo <- under_over[[x]]

  base::setdiff(a$Structure_id, uo$Structure_id) |>
    tibble::enframe(name = "Erro", value = "Structure_id") |>
    dplyr::mutate(status = "no_under_nor_over", position = "in_ct")
}) |>
  purrr::set_names(names(ct)) |>
  purrr::keep(~ nrow(.x) > 0) |> # discard datasets without errors
  dplyr::bind_rows(.id = "Dataset")

ct_diff_under_over |>
  print(n = Inf)

# A tibble: 60 × 5
   Dataset   Erro Structure_id             status            position
   <chr>    <int> <chr>                    <chr>             <chr>   
 1 Example3     1 KM 437                   no_under_nor_over in_ct   
 2 Example3     2 AR08                     no_under_nor_over in_ct   
 3 Example3     3 AR10                     no_under_nor_over in_ct   
 4 Example3     4 KM 158                   no_under_nor_over in_ct   
 5 Example3     5 KM 192                   no_under_nor_over in_ct   
 6 Example3     6 KM 248                   no_under_nor_over in_ct   
 7 Example3     7 KM 636,7                 no_under_nor_over in_ct   
 8 Example3     8 KM 701                   no_under_nor_over in_ct   
 9 Example3     9 KM 770                   no_under_nor_over in_ct   
10 Example3    10 KM 98,3                  no_under_nor_over in_ct   
11 Example3    11 ANTES DO CANTEIRO        no_under_nor_over in_ct   
12 Example3    12 AR100                    no_under_nor_over in_ct   
13 Example3    13 AR14F                    no_under_nor_over in_ct   
14 Example3    14 AR15/IGAPÓ               no_under_nor_over in_ct   
15 Example3    15 AR27                     no_under_nor_over in_ct   
16 Example3    16 AR58                     no_under_nor_over in_ct   
17 Example3    17 AR59                     no_under_nor_over in_ct   
18 Example3    18 AR60                     no_under_nor_over in_ct   
19 Example3    19 AR61                     no_under_nor_over in_ct   
20 Example3    20 AR89                     no_under_nor_over in_ct   
21 Example3    21 AR94F                    no_under_nor_over in_ct   
22 Example3    22 ÁREA VERDE DIREITA       no_under_nor_over in_ct   
23 Example3    23 ÁREA VERDE/ ESQUERDA     no_under_nor_over in_ct   
24 Example3    24 ÁREA VERDE/OAC36 (PERTO) no_under_nor_over in_ct   
25 Example3    25 CAIPIRÃO                 no_under_nor_over in_ct   
26 Example3    26 ENTRE OAC139 OAE PILÃO   no_under_nor_over in_ct   
27 Example3    27 FRAGMENTO                no_under_nor_over in_ct   
28 Example3    28 FRAGMENTO KM 153         no_under_nor_over in_ct   
29 Example3    29 FRAGMENTO KM 91          no_under_nor_over in_ct   
30 Example3    30 FRAGMENTO KM266          no_under_nor_over in_ct   
31 Example3    31 FRAGMENTO LADO PRAD LD   no_under_nor_over in_ct   
32 Example3    32 KM104                    no_under_nor_over in_ct   
33 Example3    33 KM128                    no_under_nor_over in_ct   
34 Example3    34 KM152                    no_under_nor_over in_ct   
35 Example3    35 KM191                    no_under_nor_over in_ct   
36 Example3    36 KM214                    no_under_nor_over in_ct   
37 Example3    37 KM223,3                  no_under_nor_over in_ct   
38 Example3    38 KM224                    no_under_nor_over in_ct   
39 Example3    39 KM296,7                  no_under_nor_over in_ct   
40 Example3    40 KM317                    no_under_nor_over in_ct   
41 Example3    41 KM349                    no_under_nor_over in_ct   
42 Example3    42 KM455                    no_under_nor_over in_ct   
43 Example3    43 KM501                    no_under_nor_over in_ct   
44 Example3    44 KM575                    no_under_nor_over in_ct   
45 Example3    45 KM681,4                  no_under_nor_over in_ct   
46 Example3    46 KM711                    no_under_nor_over in_ct   
47 Example3    47 KM797                    no_under_nor_over in_ct   
48 Example3    48 OAC NOVO (CAM006)        no_under_nor_over in_ct   
49 Example3    49 OAC110/KM91              no_under_nor_over in_ct   
50 Example3    50 OAC14                    no_under_nor_over in_ct   
51 Example3    51 OAC18                    no_under_nor_over in_ct   
52 Example3    52 OAC251                   no_under_nor_over in_ct   
53 Example3    53 OAC74/AR40               no_under_nor_over in_ct   
54 Example3    54 PONTE PIAUÍ KM379        no_under_nor_over in_ct   
55 Example3    55 PRÓX. PONTE DOS MILAGRES no_under_nor_over in_ct   
56 Example3    56 PRÓXIMO À OAC            no_under_nor_over in_ct   
57 Example3    57 PROXIMO A OAC 36A        no_under_nor_over in_ct   
58 Example3    58 RASTRO/DIREITA           no_under_nor_over in_ct   
59 Example3    59 OAC089                   no_under_nor_over in_ct   
60 Example3    60 OAC63                    no_under_nor_over in_ct

8.2.3.2 Crossing structures x Camera trap

We apply here the same strategy as above, but in the opposite direction.

Code

under_over_diff_ct <- purrr::map(names(ct), function(x) {
  a <- ct[[x]]
  uo <- under_over[[x]]

  base::setdiff(uo$Structure_id, a$Structure_id) |>
    tibble::enframe(name = "Erro", value = "Structure_id") |>
    dplyr::mutate(status = "not_in_ct") |>
    dplyr::left_join(under_over[[x]], by = "Structure_id")
}) |>
  purrr::set_names(names(ct)) |>
  purrr::keep(~ nrow(.x) > 0) |>
  dplyr::bind_rows(.id = "Dataset")

dplyr::bind_rows(ct_diff_under_over, under_over_diff_ct) |>
  dplyr::arrange(Dataset) |>
  print(n = Inf)

# A tibble: 67 × 5
   Dataset   Erro Structure_id               status            position
   <chr>    <int> <chr>                      <chr>             <chr>   
 1 Example1     1 BCS1                       not_in_ct         under   
 2 Example1     2 BCS2                       not_in_ct         under   
 3 Example1     3 PAEREA                     not_in_ct         over    
 4 Example3     1 KM 437                     no_under_nor_over in_ct   
 5 Example3     2 AR08                       no_under_nor_over in_ct   
 6 Example3     3 AR10                       no_under_nor_over in_ct   
 7 Example3     4 KM 158                     no_under_nor_over in_ct   
 8 Example3     5 KM 192                     no_under_nor_over in_ct   
 9 Example3     6 KM 248                     no_under_nor_over in_ct   
10 Example3     7 KM 636,7                   no_under_nor_over in_ct   
11 Example3     8 KM 701                     no_under_nor_over in_ct   
12 Example3     9 KM 770                     no_under_nor_over in_ct   
13 Example3    10 KM 98,3                    no_under_nor_over in_ct   
14 Example3    11 ANTES DO CANTEIRO          no_under_nor_over in_ct   
15 Example3    12 AR100                      no_under_nor_over in_ct   
16 Example3    13 AR14F                      no_under_nor_over in_ct   
17 Example3    14 AR15/IGAPÓ                 no_under_nor_over in_ct   
18 Example3    15 AR27                       no_under_nor_over in_ct   
19 Example3    16 AR58                       no_under_nor_over in_ct   
20 Example3    17 AR59                       no_under_nor_over in_ct   
21 Example3    18 AR60                       no_under_nor_over in_ct   
22 Example3    19 AR61                       no_under_nor_over in_ct   
23 Example3    20 AR89                       no_under_nor_over in_ct   
24 Example3    21 AR94F                      no_under_nor_over in_ct   
25 Example3    22 ÁREA VERDE DIREITA         no_under_nor_over in_ct   
26 Example3    23 ÁREA VERDE/ ESQUERDA       no_under_nor_over in_ct   
27 Example3    24 ÁREA VERDE/OAC36 (PERTO)   no_under_nor_over in_ct   
28 Example3    25 CAIPIRÃO                   no_under_nor_over in_ct   
29 Example3    26 ENTRE OAC139 OAE PILÃO     no_under_nor_over in_ct   
30 Example3    27 FRAGMENTO                  no_under_nor_over in_ct   
31 Example3    28 FRAGMENTO KM 153           no_under_nor_over in_ct   
32 Example3    29 FRAGMENTO KM 91            no_under_nor_over in_ct   
33 Example3    30 FRAGMENTO KM266            no_under_nor_over in_ct   
34 Example3    31 FRAGMENTO LADO PRAD LD     no_under_nor_over in_ct   
35 Example3    32 KM104                      no_under_nor_over in_ct   
36 Example3    33 KM128                      no_under_nor_over in_ct   
37 Example3    34 KM152                      no_under_nor_over in_ct   
38 Example3    35 KM191                      no_under_nor_over in_ct   
39 Example3    36 KM214                      no_under_nor_over in_ct   
40 Example3    37 KM223,3                    no_under_nor_over in_ct   
41 Example3    38 KM224                      no_under_nor_over in_ct   
42 Example3    39 KM296,7                    no_under_nor_over in_ct   
43 Example3    40 KM317                      no_under_nor_over in_ct   
44 Example3    41 KM349                      no_under_nor_over in_ct   
45 Example3    42 KM455                      no_under_nor_over in_ct   
46 Example3    43 KM501                      no_under_nor_over in_ct   
47 Example3    44 KM575                      no_under_nor_over in_ct   
48 Example3    45 KM681,4                    no_under_nor_over in_ct   
49 Example3    46 KM711                      no_under_nor_over in_ct   
50 Example3    47 KM797                      no_under_nor_over in_ct   
51 Example3    48 OAC NOVO (CAM006)          no_under_nor_over in_ct   
52 Example3    49 OAC110/KM91                no_under_nor_over in_ct   
53 Example3    50 OAC14                      no_under_nor_over in_ct   
54 Example3    51 OAC18                      no_under_nor_over in_ct   
55 Example3    52 OAC251                     no_under_nor_over in_ct   
56 Example3    53 OAC74/AR40                 no_under_nor_over in_ct   
57 Example3    54 PONTE PIAUÍ KM379          no_under_nor_over in_ct   
58 Example3    55 PRÓX. PONTE DOS MILAGRES   no_under_nor_over in_ct   
59 Example3    56 PRÓXIMO À OAC              no_under_nor_over in_ct   
60 Example3    57 PROXIMO A OAC 36A          no_under_nor_over in_ct   
61 Example3    58 RASTRO/DIREITA             no_under_nor_over in_ct   
62 Example3    59 OAC089                     no_under_nor_over in_ct   
63 Example3    60 OAC63                      no_under_nor_over in_ct   
64 Example3     1 OAC36A                     not_in_ct         under   
65 Example3     2 OACNOVO                    not_in_ct         under   
66 Example3     3 PRÓXIMO À OAC/ ESTACA 6555 not_in_ct         under   
67 Example3     4 OAC074                     not_in_ct         under

8.2.4 Check if fences are present on underpasses

Firstly we read the Fencing spreadsheet for every dataset, filtering for only those who have any row filled.

Code

fences <- read_sheet(path = "Example", sheet = "Fencing", na = "NA") |>
  purrr::map(
    ~ .x |>
      dplyr::mutate(
        Structure_id = as.character(stringr::str_to_upper(Structure_id)),
        position = "under",
        .keep = "none"
      )
  ) |>
  purrr::keep(~ nrow(.x) > 0)

We then check if all the Structure_id listed on the Fencing spreadsheet are comprised on the Structure_id on the Underpasses spreadsheet.

Code

fences_diff_under <- purrr::map(names(fences), function(x) {
  a <- fences[[x]]
  u <- under[[x]]

  base::setdiff(a$Structure_id, u$Structure_id) |>
    tibble::enframe(name = "Erro", value = "Structure_id") |>
    dplyr::mutate(status = "no_under", position = "in_fences")
}) |>
  purrr::set_names(names(fences)) |>
  purrr::keep(~ nrow(.x) > 0) |>
  dplyr::bind_rows(.id = "Dataset")

fences_diff_under |>
  print(n = Inf)

# A tibble: 0 × 0

We then proceed on the opposite direction, checking if there are Structure_id on the Underpasses spreadsheet not encompassed on Fencing. Obviously, there are passages that could not have been fenced, however, we play it conservatively to check with the authors if this is the case or if they have forgotten to fill the Fencing spreadsheet correctly.

Code

under_fences_diff <- purrr::map(names(fences), function(x) {
  a <- fences[[x]]
  u <- under[[x]]

  base::setdiff(u$Structure_id, a$Structure_id) |>
    tibble::enframe(name = "Erro", value = "Structure_id") |>
    dplyr::mutate(status = "no_fences", position = "in_under")
}) |>
  purrr::set_names(names(fences)) |>
  purrr::keep(~ nrow(.x) > 0) |>
  dplyr::bind_rows(.id = "Dataset")

under_fences_diff |>
  print(n = Inf)

# A tibble: 0 × 0